package dao;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import common.Constant;
import model.SinhVienModel;
import util.DataUtil;

public class SinhVienDAO {	
	public SinhVienDAO(){	}
	
	public static ArrayList<SinhVienModel> getAllSinhVienByTen(int indexStart,int total,String tenSV,String diaChi)
	{
		ArrayList<SinhVienModel> sinhVienListModel=new ArrayList<SinhVienModel>();
		int rsTotalPage=0;

		if (indexStart > 1)
	     {			 
	         indexStart = (indexStart * total) - (total -1);
	         rsTotalPage=total+indexStart-1;	         
	     }
		 else if (indexStart == 1)
	         rsTotalPage=total;

		try {

			CallableStatement csmt = DataUtil
				.getConnection()
				.prepareCall("{call sp_SinhVien_GetSinhVienByTen(?,?,?,?)}");
			csmt.setInt("IndexStart",indexStart);
			csmt.setInt("Total",rsTotalPage);
			csmt.setNString("TenSV","%"+tenSV+"%");
			csmt.setNString("DiaChi","%"+diaChi+"%");
			
			ResultSet rs = DataUtil.executeStore(csmt);

			SinhVienModel sinhVienModel;
			while(rs.next())
			{
				sinhVienModel=new SinhVienModel();
				sinhVienModel.setSTT(indexStart+"");
				sinhVienModel.setMaSV(rs.getString("MaSV"));
				sinhVienModel.setTenSV(rs.getNString("Ten_SV"));
				sinhVienModel.setNgaySinh(rs.getString("Ngay_Sinh"));
				sinhVienModel.setGioiTinh(rs.getString("Gioi_Tinh"));
				sinhVienModel.setDiaChi(rs.getString("Dia_Chi"));
				sinhVienListModel.add(sinhVienModel);
				indexStart++;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}

		return sinhVienListModel;
	}

	public static int getTotalSinhVienByTen(String tenSV, String diaChi)
	{
		try {
			CallableStatement csmt = DataUtil
				.getConnection()
				.prepareCall("{call sp_SinhVien_GetTotalSinhVienByTen(?,?)}");
			
			csmt.setNString("TenSV","%"+tenSV+"%");
			csmt.setNString("DiaChi","%"+diaChi+"%");
			
			ResultSet rs = DataUtil.executeStore(csmt);
			
			while(rs.next())
			{
				return rs.getInt("Total"); 
			}
		} catch (Exception e) {
			e.printStackTrace();
			return 0;
		}
		return 0;
	}
	
	public static ArrayList<String> showNumPage(int i,int total,String tenFile)
	{
		String str="";
		ArrayList<String> strList=new ArrayList<String>();
		int j=i;
         if (i > 1)
             i = i - 1;
         
         
             while (i <= (total) / Constant.NUM_RECORD_SinhVien)
             {
            	 if(i==j)
            		 str="<a href='"+tenFile+"?index=" + i + "' style='color:blue;background-color:yellow;text-decoration:none'>"+i+"</a>";
            	 else
            		 str="<a href='"+tenFile+"?index=" + i + "' style='color:blue;text-decoration:none'>"+i+"</a>";
                 strList.add(str);                 
            	 i++;
             }
             if ( total % Constant.NUM_RECORD_SinhVien != 0 && total > Constant.NUM_RECORD_SinhVien)
             {
            	 if(i==j)
            		 str="<a href='"+tenFile+"?index=" + i + "' style='color:blue;background-color:yellow;text-decoration:none'>"+i+"</a>";
            	 else
            		 str="<a href='"+tenFile+"?index=" + i + "' style='color:blue;text-decoration:none'>"+i+"</a>";
                 strList.add(str);
             }

		return strList;
	}
	
	public static Boolean insertSinhVien(SinhVienModel sinhVienModel){
		try {
			CallableStatement csmt = DataUtil
				.getConnection()
				.prepareCall("{call sp_SinhVien_InsertSinhVien(?,?,?,?,?)}");
			csmt.setNString("MaSV",sinhVienModel.getMaSV());
			csmt.setNString("Ten_SV",sinhVienModel.getTenSV());
			csmt.setString("Ngay_Sinh",sinhVienModel.getNgaySinh());
			csmt.setString("Gioi_Tinh",sinhVienModel.getGioiTinh());
			csmt.setString("Dia_Chi",sinhVienModel.getDiaChi());
	
			return DataUtil.executeNonStore(csmt);
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
		
	}
	
	
	public static Boolean updateSinhVienByMaSV(SinhVienModel sinhVienModel){
		
		try {
			CallableStatement csmt = DataUtil
				.getConnection()
				.prepareCall("{call sp_SinhVien_UpdateSinhVienByMaSV(?,?,?,?,?)}");
			csmt.setString("MaSV",sinhVienModel.getMaSV());
			csmt.setNString("Ten_SV",sinhVienModel.getTenSV());
			csmt.setNString("Ngay_Sinh",sinhVienModel.getNgaySinh());
			csmt.setString("Gioi_Tinh",sinhVienModel.getGioiTinh());
			csmt.setString("Dia_Chi",sinhVienModel.getDiaChi());
	
			return DataUtil.executeNonStore(csmt);
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
	}
	
	public static Boolean deleteSinhVien(String maSinhVien) {
		Boolean result = false;		
		try {
			CallableStatement csmt = DataUtil
				.getConnection()
				.prepareCall("{call sp_SinhVien_DeleteSinhVien(?)}");
			
			csmt.setString("MaSV",maSinhVien);
			
			result = DataUtil.executeNonStore(csmt);			
		} catch (Exception e) {
			e.printStackTrace();
		}						
		return result;
	}
	
	/*chi tiet sinh vien*/
	public static SinhVienModel XemChiTietSinhVienByMaSinhVien(String maSinhVien) {
		SinhVienModel sinhVienModel = new SinhVienModel();
		try {
			PreparedStatement preparedStatement = DataUtil
					.getConnection()
					.prepareStatement(
							Constant.SQL_RES
									.getString("sql.showChiTietSinhVienByMaSinhVien"));
			preparedStatement.setString(1, maSinhVien);			
			ResultSet rs = preparedStatement.executeQuery();
			while (rs.next()) {
				sinhVienModel.setMaSV(rs.getString("MaSinhVien"));
				sinhVienModel.setTenSV(rs.getString("TenSinhVien"));
				sinhVienModel.setNgaySinh(rs.getString("NgaySinh"));
				sinhVienModel.setGioiTinh(rs.getString("GioiTinh"));
				sinhVienModel.setDiaChi(rs.getString("DiaChi"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return sinhVienModel;
	}
	
	// dang ky
	
	public static ArrayList<SinhVienModel> getAllSinhVien() {
		ArrayList<SinhVienModel> listKetQua = new ArrayList<SinhVienModel>();
		try {
			PreparedStatement preparedStatement = DataUtil.getConnection()
					.prepareStatement(
							Constant.SQL_RES.getString("sql.getAllSinhVien"));
			ResultSet rs = preparedStatement.executeQuery();
			while (rs.next()) {
				SinhVienModel sinhVienModel = new SinhVienModel();
				
				sinhVienModel.setMaSV(rs.getString("MaSinhVien"));
				sinhVienModel.setTenSV(rs.getString("TenSinhVien"));
				sinhVienModel.setNgaySinh(rs.getString("NgaySinh"));
				sinhVienModel.setGioiTinh(rs.getString("GioiTinh"));
				sinhVienModel.setDiaChi(rs.getString("DiaChi"));
				
				listKetQua.add(sinhVienModel);
				
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return listKetQua;
	}
	
//dang nhap quyen sinh vien
	
	public static SinhVienModel XemTenSinhVien(String maSinhVien) {
		SinhVienModel sinhVienModel = new SinhVienModel();
		try {
			PreparedStatement preparedStatement = DataUtil
					.getConnection()
					.prepareStatement(
							Constant.SQL_RES
									.getString("sql.showTenSinhVien"));
			preparedStatement.setString(1, maSinhVien);			
			ResultSet rs = preparedStatement.executeQuery();
			while (rs.next()) {
				sinhVienModel.setMaSV(rs.getString("MaSinhVien"));
				sinhVienModel.setTenSV(rs.getString("TenSinhVien"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return sinhVienModel;
	}
	
	public static ArrayList<SinhVienModel> showAllSinhVien() {
		ArrayList<SinhVienModel> listKetQua = new ArrayList<SinhVienModel>();
		try {
			PreparedStatement preparedStatement = DataUtil.getConnection()
					.prepareStatement(
							Constant.SQL_RES.getString("sql.getAllSinhVien"));
			ResultSet rs = preparedStatement.executeQuery();
			while (rs.next()) {
				SinhVienModel sinhVienModel = new SinhVienModel();
				sinhVienModel.setMaSV(rs.getString("MaSinhVien"));
				sinhVienModel.setTenSV(rs.getString("TenSinhVien"));

				listKetQua.add(sinhVienModel);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return listKetQua;
	}
}
